package com.blog.service.Impl;


import com.blog.config.SpringContextHolder;
import com.blog.service.SysUpdateDatabase;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.context.ConfigurableWebApplicationContext;
import org.thymeleaf.expression.Lists;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author cgx
 * @Date 2022/1/19 16:49
 */
@Service
public class SysUpdateDatabaseImpl implements SysUpdateDatabase {


    Logger logger = LoggerFactory.getLogger(SysUpdateDatabaseImpl.class);

    private static final String DIC_SQL_SIGN = "executeSqlDate";

    @Autowired
    private DataSource dataSource;

    @Autowired
    JdbcTemplate jdbcTemplate;


    @Override
    public void updateDatabase() throws IOException, ParseException {
        logger.info("数据库脚本开始更新");
        ConfigurableWebApplicationContext context = (ConfigurableWebApplicationContext) SpringContextHolder.getApplicationContext();
        org.springframework.core.io.Resource[] resources = context.getResources("classpath:blog/sql/*.sql");
        updateDbSql(resources);
    }

    /**
     * 升级数据库
     * @param resources
     * @return
     */
    private void updateDbSql(Resource[] resources) throws IOException, ParseException {
        List<Object[]> list = new ArrayList<>();
        List<Object[]> insertDictObjs = new ArrayList<>();
        TreeMap<Long, List<String>> mapSql = new TreeMap<>();
        //获取sql目录下资源
        for (Resource resource : resources){
            logger.info("开始处理数据库脚本: {}",resource.getFilename());
            mapSql = collectSqlByFile(mapSql, insertDictObjs, resource);
        }

        System.err.println(mapSql);
    }

    private TreeMap<Long, List<String>> collectSqlByFile(TreeMap<Long, List<String>> mapSql, List<Object[]> insertDictObjs, Resource resource) throws IOException, ParseException {
        Reader reader = new InputStreamReader(resource.getInputStream(), "utf-8");
        BufferedReader br = new BufferedReader(reader);

        //判断文件是否存在
        String fileName = resource.getFilename();
        if (fileName == null) {
            return null;
        }
        //获取改SQL文件在数据库中记录的最后执行信息
        Date dbSqlDate = getSqlExecuteDate(fileName);
        //当前SQL文件遍历到的最新标记日期
        Date curSqlDate = dbSqlDate;
        StringBuilder sql = new StringBuilder();
        String tempChar;
        int line = -1;
        //记录是否收集此行sql
        boolean isExecuteSql = false;
        //sql块标记状态. 0:没有块;1:块开始;2:块结束
        int blockTagsState = 0;
        while ((tempChar = br.readLine()) != null){
            line++;
            //记录是否停止
            boolean isBreak = true;

            //处理( /**/类型 )注释行和空文本行
            if (tempChar.startsWith("/*") || tempChar.startsWith("*") || tempChar.length() == 0) {
                continue;
            }
            //处理(--类型)注释行
            if (tempChar.startsWith("--")){
                //切割掉开头日期标识
                String[] strings = tagsValue(tempChar);
                if (strings != null){
                    if("date".equals(strings[0])){
                        Date sqlDate = DateUtils.parseDate(strings[1], "yyyy-MM-dd");
                        if (dbSqlDate == null || sqlDate.getTime() > dbSqlDate.getTime()) {
                            curSqlDate = sqlDate;
                            //开始收集需要执行的sql
                            isExecuteSql = true;
                            blockTagsState = 0;
                        }
                    }else {
                        logger.error("解析标签错误 : fileName = {} ,line = {},tag = {} ,value='{}'", fileName, line, strings[0], strings[1]);
                    }
                }
            }
            if (!isExecuteSql) {
                continue;
            }
            long keySql = curSqlDate.getTime();
            List<String> list = null;
            if (mapSql.containsKey(keySql)) {
                list = mapSql.get(keySql);
            } else {
                list = new ArrayList<String>();
                logger.debug("add new date {}", keySql);
            }

            if (blockTagsState == 0 && tempChar.endsWith(";")) {
                sql.append(" ");
                sql.append(tempChar.substring(0, tempChar.length() - 1));
                list.add(sql.toString());
                mapSql.put(keySql, list);
                sql.setLength(0);// 重新添加SQL语句
                continue;
            }else {
                if (blockTagsState == 2) {
                    // 脚本块结束,加入代码中
                    sql.append(" ");
                    String _sql = sql.toString();
                    if (StringUtils.isNotBlank(_sql)) {
                        list.add(sql.toString());
                        mapSql.put(keySql, list);
                    }
                    sql.setLength(0);// 重新添加SQL语句
                    continue;
                } else {
                    // 脚本块内语句
                    sql.append(" ").append(tempChar).append(" ");
                }
            }
        }


        if (curSqlDate != null) {
            try {
                if (dbSqlDate == null) {
                    createSqlLatestDate(insertDictObjs, fileName, curSqlDate);
                } else {
                    updateSqlLatestDate(fileName, curSqlDate);
                }
            } catch (Exception e) {
                logger.info(e.getMessage());
            }
        }

        reader.close();
        br.close();
        return mapSql;
    }


    /**
     * 封装字典更新日期
     */
    private void createSqlLatestDate(List<Object[]> insertDictObjs, String fileName, Date date) {
        Object[] objs = new Object[5];
        //id
        objs[0] = "sql_" + fileName;
        //sign
        objs[1] = DIC_SQL_SIGN;
        //code
        objs[2] = 0;
        //name
        objs[3] = fileName;
        String dateValue = DateFormatUtils.format(date, "yyyy-MM-dd");
        //value
        objs[4] = dateValue;
        insertDictObjs.add(objs);
    }

    /**
     *
     * @param fileName 文件名
     * @param date 日期
     * @return
     */
    private void updateSqlLatestDate(String fileName, Date date) {
        String dateValue = DateFormatUtils.format(date, "yyyy-MM-dd");
        String sql = "update pt_dictionary set value=? where sign=? and name=?";
        jdbcTemplate.update(sql, dateValue, DIC_SQL_SIGN, fileName);
    }

    private Date getSqlExecuteDate(String fileName){
        try {
            String sql = "select value from sys_dictionary where sign = ? and name = ?";
            List<Map<String, Object>> dataLists = jdbcTemplate.queryForList(sql, DIC_SQL_SIGN, fileName);
            if (dataLists!= null && dataLists.size() > 0){
                Map<String, Object> dataMaps = dataLists.get(0);
                String dateValue = (String) dataMaps.get("value");
                if (dateValue != null) {
                    Date latestDate = DateUtils.parseDate(dateValue, dateValue);
                    return latestDate;
                }
            }
        }catch (Exception e){
            logger.info(fileName + " : " + e.getMessage(), e);
        }
        return null;
    }

    private static String[] tagsValue(String lineSql) {
        String regex = ".*\\[(.+?)\\].*";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(lineSql);
        String value[] = null;
        if (matcher.matches()) {
            String _v = matcher.group(1);
            if (_v != null) {
                value = _v.split("=");
                if (value.length != 2) {
                    value = null;
                } else {
                    value[0] = value[0].trim();
                    value[1] = value[1].trim();
                }
            }
        }
        return value;
    }



}
